set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode='nonstrict';
set hive.exec.max.dynamic.partitions=400;
set hive.exec.max.dynamic.partitions.pernode=400;


insert overwrite table jms_dm.dm_route_city_control_dt
select
     all_cnt
    ,ky_cnt    
    ,my_cnt    
    ,cp_cnt    
    ,yc_cnt    
    ,case when yc_cnt < 200 then 1 end as if_warning
    ,dt
from (
    select
         count(1) as all_cnt
        ,sum(if(effe_type = '快于',1,0)) as ky_cnt
        ,sum(if(effe_type = '慢于',1,0)) as my_cnt
        ,sum(if(effe_type = '持平',1,0)) as cp_cnt
        ,sum(if(effe_type is null ,1,0)) as yc_cnt
        ,dt
    from (
        select  
             in_from_city_desc
            ,out_to_city_desc
            ,substr(total_days_t,3,5) 
            ,case when substr(total_days_t,3,5)  = effective_times_cn then '持平'
                  when substr(total_days_t,3,5)  < effective_times_cn then '快于'
                  when substr(total_days_t,3,5)  > effective_times_cn then '慢于'
               end as effe_type
            ,is_main_city
            ,dt 
        from jms_dm.dm_fastest_route_area_effective
        where dt = '{{ execution_date |  cst_ds }}'
        and is_main_city = 1 
    ) a group by dt 
) a 
distribute by dt,pmod(hash(rand()),1);